Pyramid's Formulate toolset enables non-technical users to build advanced business logic calculations, lists, KPIs, parameters, and scripts using a point-and-click interface. The code editor extends this functionality with an integrated development environment ("IDE") for advanced users to write both PQL and MDX statements, complete with drag-and-drop function libraries and metadata models.
The IDE is accessible from the following designers:
Access the Script Editor
To access the IDE, click Script Mode from the top ribbon:
Note that:
- Any logic already added to the canvas will appear in script mode
- Once in script mode, you cannot go back to the canvas view
- You can add scripts from other locations; for example, copy and paste scripts, or drop files containing scripts, or drop metadata from Microsoft SQL Server Management Studio onto the script window
- Use the Metadata panel to insert the relevant metadata into the script
Script Editor Workspace in Formulate
- From the Script Editor, drag the function you want to use from the Functions listing (red arrow below) onto the canvas (orange highlight).
- From the Metadata panel (blue arrow below), inject the required model metadata into the function.
- Use the Style options in the ribbon (blue highlight) to set the design theme and text size. The design theme determines the background color of the script editor, and the text color.
- As usual, click Run (green arrow) to test the function; the results are displayed in the Result Preview panel (purple highlight).
Note: You can search for the function you want, and hover over each function to see a tooltip about it (green highlight).
Navigating Custom Formulations in the Script Editor
You can add existing custom formulations from the Metadata panel to your custom script:
When you add custom formulations from the Metadata panel to the script editor, you can hover over the Item ID in the script to see a tooltip displaying the item name, its folder location, who created it, the create date, modification date, item ID, and formula type.
You can also open the selected formula in the graphical user interface (GUI) by holding Ctrl and clicking on the Item ID.
Script Language
The script language is PQL, unless using OLAP, Tabular, or BW models, in which case the script language is MDX. To review a reference library of MDX functions, see Microsoft's MDX Function Reference.
The script editor allows advanced users to construct PQL and MDX formulations using code, rather than the nodes used in the main Custom List and Custom Member designers.
Use the ChatGPT AI-driven PQL function (from the Common PQL functions) to write PQL expressions and generate various assets. This can be used to add additional information and background to your presentation; it cannot tell you about your data set. The Generative PQL Function is supported in:
- Click here to learn how the PQL Editor works.
Use AI to Generate Your PQL
Use the Generative AI integration to generate PQL or MDX code for custom formulas, lists, KPIs, and shared custom scripts.
Warning: AI-generated assets are generated from public domain algorithms, which can produce both erroneous and inconsistent or random results. Use at your own risk.